

***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


******************************************************************************************
******************************************************************************************
 
	*This files drop the double reported trade between two reporting dealers. 
	*Note that we cannot do this for the ``other dealer" (number 96)
	
	*Here we define a trade to be the same if the following is the same 
	*(bidderid/lei-group, date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue)

	*Caveat of current method: 
	*It may be that we are deleting sell-side trade of bidderid=71 + LEI=88 double counted 
	*with a buy-side trade of bidderid=71 + LEI=88. 
	*This is incorrect because 71 should become the LEI in the double counted trade. 
	*From eyeballing the data this doesn't seem to matter much.  
	
******************************************************************************************
******************************************************************************************


*client types
preserve
keep if market_ty=="secondary"
collapse IIROC_QUANTITY, by(LEI  client_type_bond_bill_ddgs client_type_MSR c_type)
keep if client_type_MSR!=""
drop IIROC_QUANTITY
tempfile tempclients
save `tempclients', replace
restore


*dummy for who needs to report 	
gen b_rep_cash = 0
replace b_rep_cash =1  if inlist(bidderid, 24, 18, 94, 88, 83, 77) 		
replace b_rep_cash =1  if  inlist(bidderid, 71, 67, 61, 46, 93) 		    
replace b_rep_cash =1  if  inlist(bidderid, 31, 26, 18001, 24001, 46001, 190, 191) 	
					

gen c_rep_cash = 0
replace c_rep_cash =1  if inlist(LEI, "24", "18", "94", "88", "83", "77") 		
replace c_rep_cash =1  if  inlist(LEI, "71", "67", "61", "46", "93") 		    
replace c_rep_cash =1  if  inlist(LEI, "31", "26", "18001", "24001", "46001", "190", "191") 							
																		
tempfile tempbase
save `tempbase', replace


*data set without reporting dealers
keep if market_type!="secondary" | ///
		(market_type=="secondary"  & (b_rep_cash!=1 | c_rep_cash!=1)) 
		
	
*store the data that excludes inter-dealer trades --> will be used later to append inter-dealer data
tempfile tempADD
save `tempADD', replace		


*keep only cash transactions between reporting dealers
use `tempbase', clear
keep if market_type=="secondary" 
keep if b_rep_cash==1 & c_rep_cash==1


*generate a group dummy
destring LEI, replace 
levelsof bidderid, local(id) 
levelsof LEI, local(lei) 
gen double group =.
format group %20.0g
foreach i of local id {
	foreach k of local lei {
		replace group = `i'`k' if (bidderid == `i' & LEI ==`k') | (LEI == `i' & bidderid ==`k')
	}
}


*find the transactions that within a day are double reported
bys group date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue: gen groupN = _N    //for now I do it per date

 
*find the transactions that are reported double (correctly) 
bys group date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue IIROC_SIDE: gen a0 = _N if IIROC_SIDE=="SELL"
bys group date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue: 			 egen group_sell = max(a0) 
replace group_sell=0 if group_sell==.
drop a0

bys group date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue IIROC_SIDE: gen a0 = _N if IIROC_SIDE=="BUY"
bys group date settlement_date isin IIROC_QUANTITY IIROC_PRICE trading_venue: 			 egen group_buy = max(a0) 
replace group_buy=0 if group_buy==.
drop a0

count 
count if group_sell==group_buy // these are the transactions that are correctly reported double 
count if group_sell!=group_buy 


*delete one of each pair of correctly double reported transaction 
drop if IIROC_SIDE=="SELL" & group_sell==group_buy 



*** Expand so that all transactions have a leg - first for repo then for sell/buy back
count
expand 2 , generate(a1)

gen bidderid_org 	= bidderid
gen LEI_org 		= LEI
gen IIROC_SIDE_org  = IIROC_SIDE

replace IIROC_SIDE = "SELL" 		if IIROC_SIDE_org=="BUY"  & a1==1
replace IIROC_SIDE = "BUY" 			if IIROC_SIDE_org=="SELL" & a1==1

replace bidderid   = LEI_org       	if a1==1 
replace LEI   	   = bidderid_org   if a1==1 
gen Dleg =(a1==1) 				
label var Dleg "is the second leg of a dealer-dealer trade. Set Dleg=0 when computing volumne"

replace client_type_MSR="" 				if a1==1
replace client_type_bond_bill_ddgs="" 	if a1==1
drop a1

tab IIROC_SIDE
tostring LEI, replace 


*correct all types of the counter-parties
merge m:1 LEI using `tempclients', update

	*_merge==2 of LEIs in the original data that are not reporting dealers, so are not in the data set with only reporting dealers
	drop if _merge==2
	drop _merge

*clean up - not needed	
drop bidderid_org LEI_org IIROC_SIDE_org



*** Bring back all the other trades
append using `tempADD'
replace Dleg=0 if Dleg==. 


